Excel VBA by Peter Bradley

Author:Peter Bradley
Language: eng
Format: epub
Tags: access excel, access vba, add macro to excel, basic excel training, basic excel tutorial, excel 2016, excel access, excel book, excel code, excel for beginners, excel guide, excel macro basics, excel macro coding, excel macro examples, excel macro programming, excel macro tutorial, excel macro vba, excel macros for dummies, excel program, excel record macro, excel tutorial for beginners, excel vba, excel vba cells, excel vba code, excel vba find, excel vba for, excel vba form, excel vba programming, excel vba programming for dummies, excel vba programming tutorial, excel vba range, excel vba sheets, excel vba tutorial, excel vba with, excel with business, how to create a macro in excel, learn excel, learn macros, learn macros in excel, learn microsoft excel, learn programming, learn to code, learn vba, learn vba for excel, macro code, macro excel, macro program, macro vba, macros in excel for beginners, microsoft excel, microsoft excel basics, microsoft excel macros, microsoft excel tutorial, microsoft office excel, microsoft visual basic for applications, ms excel program, ms office excel, vba basics
Publisher: Peter Bradley
Published: 2019-06-30T00:00:00+00:00

Chapter 4: Looping

Looping is an immensely useful programming technique that will make it possible for you to run through several ranges quickly while only requiring the addition of a small amount of extra code.

Single loop: The first type of loop, the single loop, can be sued to easily move through ranges of cells that are one-dimensional. An example of this can be connected to the command button with the following code:

Dim i As Integer

For j = 2 To 7

Cells(j, 1).Value = 75

Next i

Assuming everything is written properly, pressing the button at this junction will place the integer (75) in J2 – J7. This occurs because the line of code between For and Next is then executed five additional times. The first time that J = 1, the VBA knows to place the integer 75 into the cell where the column and row meet one another. From there, you will need for the VBA to hit the second j in order to increase the amount by 1 and thus reset everything to the For statement. Next, when j = 2, the VBA then provides a value in the form of 75 into the cell that exists at the next point where the row and column intersect. This then continues for all of the various cells and rows between 2 and 7. While this is not expressly required, with this type of code you are going to need to be in the habit of indenting to keep things as legible as possible. Specifically, this will comes into play between the words For and Next as this should make things easier to read when it comes time to find errors.

Double loop: A double loop is, as the name implies, a loop that makes a movement through a full two-dimensional cell range. In order to use one, you can use the following code and attach it to your command button:

Dim j As Integer, k As Integer

For j = 2 To 7

For k = 1 To 2

Cells(j, k).Value = 75

Next k

Next j

Assuming everything has been done correctly you will then find that this fills rows 2 – 7 of the columns k and j with the number 75. This code can then tell the VBA that when j = 2 and k =1 then it needs to enter 75 wherever the two meet initially, from there, it increases by 1 before returning to For in the k statement. Next, when j = 2 and k =2 then VBA knows to place 75 where they intersect again. Based on the code, the VBA will then ignore j moving forward as it will only be running between 1 and 2. This is then repeated until the VBA has run through all of the j columns that meet the desired criteria.

Triple loop: A triple loop is much the same as a double loop with the exception that it works across numerous worksheets. In order to use one effectively, you can use the following code:

Dim d As Integer, j As Integer, k As Integer

For d = 2 To 4

For j = 2 To 7

For k = 2 To 3

Worksheets(d).

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.